package com.geline.sqlbackup.service;

import cn.hutool.core.date.BetweenFormatter;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.cron.CronUtil;
import cn.hutool.cron.task.Task;
import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.druid.DbType;
import com.geline.sqlbackup.bean.InformationTable;
import com.geline.sqlbackup.config.DatabaseBean;
import com.geline.sqlbackup.config.DatabaseItem;
import com.geline.sqlbackup.config.SqlBackupProperties;
import com.geline.sqlbackup.enums.StatusEnum;
import com.geline.sqlbackup.util.JdbcDbTypeUtil;
import com.geline.sqlbackup.util.JdbcParseUtils;
import com.geline.sqlbackup.util.SqlBackupUtils;
import com.geline.sqlbackup.util.SqlInformationUtils;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.io.File;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库表备份服务类
 * @author: mengx
 * @date: 2021/10/18 17:49
 */
@Slf4j
public class SqlBackupService {

    private SqlBackupProperties properties;
    private SqlBackupLogService logService;

    public SqlBackupService(SqlBackupProperties properties, SqlBackupLogService logService){
        this.properties = properties;
        this.logService = logService;
    }

    public void start() {
        String key = properties.getKey();
        String jdbcUrl = SpringUtil.getProperty(key + ".url");
        String catalog = JdbcParseUtils.findCatalogByUrl(jdbcUrl);
        String dbName = JdbcParseUtils.findSchemaName(jdbcUrl);
        if(StrUtil.isBlank(dbName)){
            dbName = catalog;
        }
        String cron = properties.getCron();
        Objects.requireNonNull(cron, "can not find cron!");

        List<DatabaseItem> list = new ArrayList<>();
        List<DatabaseBean> beans = this.properties.getList();
        if(beans!=null && beans.size()>0){
            for (DatabaseBean bean : beans){
                list.add(getDatabaseItem(bean, jdbcUrl));
            }
        }else {
            list.add(getDatabaseItem(null, jdbcUrl));
        }

        for (DatabaseItem item : list){
            String newCron = item.getCron();
            newCron = StrUtil.isNotBlank(newCron) ? newCron : cron;
            this.addTask(newCron, item, dbName);
            log.info("success to addTask catalog={}, dbName={}, cron = {}", catalog, dbName, newCron);
        }
        // 支持秒级别定时任务 CronUtil.setMatchSecond(true);
        if(!CronUtil.getScheduler().isStarted()){
            CronUtil.start();
        }
        log.info("启动定时任务[备份数据库][database-backup]！");
    }

    private DatabaseItem getDatabaseItem(DatabaseBean bean, String jdbcUrl){
        String key = properties.getKey();
        String savePath = properties.getSavePath();
        Objects.requireNonNull(savePath, "can not find savePath!");
        if(!savePath.endsWith("/")){
            savePath = savePath+"/";
        }

        DatabaseItem databaseItem = new DatabaseItem();
        databaseItem.setCron(bean.getCron());
        databaseItem.setJdbcUrl(jdbcUrl);
        databaseItem.setSavePath(savePath);
        databaseItem.setUsername(SpringUtil.getProperty(key + ".username"));
        databaseItem.setPassword(SpringUtil.getProperty(key + ".password"));
        databaseItem.setDriverClassName(SpringUtil.getProperty(key + ".driver-class-name"));
        databaseItem.setMaxHistoryDay(properties.getMaxHistoryDay()==null ? 30 : properties.getMaxHistoryDay());
        if(bean!=null){
            databaseItem.setIgnoreTablePrefixes(bean.getIgnoreTablePrefixes());
            databaseItem.setIgnoreTables(bean.getIgnoreTables());
            databaseItem.setBackupTablePrefixes(bean.getBackupTablePrefixes());
            databaseItem.setBackupTables(bean.getBackupTables());
        }
        return databaseItem;
    }

    /**
     * 添加定时任务并启动
     * @param cron
     * @param databaseItem
     */
    protected void addTask(String cron, DatabaseItem databaseItem, String dbName){
        //添加定时任务
        CronUtil.schedule(cron, new Task() {
            @Override
            public void execute() {
                String fileName = String.format("%s%s_%s_%s.sql", databaseItem.getSavePath(), dbName, DateUtil.format(new Date(), "yyyyMMdd"), RandomUtil.randomString(4));
                log.info("start to run task : {}", fileName);
                long startTime = System.currentTimeMillis();
                //执行备份SQL
                saveDataBaseBackupSQL(databaseItem, dbName, fileName);
                long endTime = System.currentTimeMillis();
                String timeStr = DateUtil.formatBetween(endTime - startTime, BetweenFormatter.Level.SECOND);
                String message = String.format("备份SQL: %s, 用时: %s", fileName, timeStr);
                logService.saveLog(StatusEnum.SUCCESS, "定时任务", message);
                //执行删除过期文件
                deleteFileOverTime(databaseItem, dbName);
            }
        });
    }

    /**
     * 删除过期文件: 最大只保留最新maxHistory个
     * @param databaseItem
     * @param dbName
     */
    public void deleteFileOverTime(DatabaseItem databaseItem, String dbName){
        String savePath = databaseItem.getSavePath();
        //String fileNameFormat = String.format("%s%s_%s.sql", savePath, catalog, DateUtil.format(new Date(), "yyyyMMdd")+"_"+ RandomUtil.randomString(4));

        Integer maxHistoryDay = databaseItem.getMaxHistoryDay();
        List<String> fileNameList = FileUtil.listFileNames(savePath);
        if(fileNameList != null && fileNameList.size()>0){
            //保存文件时间为N天以内
            DateTime dateTime = DateUtil.offsetDay(new Date(), -maxHistoryDay);
            long maxHistoryTime = dateTime.getTime();
            fileNameList.stream().forEach(row -> {
                if(row.contains(dbName)){
                    File file = new File(savePath+"/"+row);
                    long lastModified = file.lastModified();
                    if(lastModified < maxHistoryTime){
                        //如果文件的最后更新时间小于保留时间，则删除
                        if(file.delete()){
                            String message = "删除SQL : "+file.getAbsoluteFile();
                            logService.saveLog(StatusEnum.SUCCESS, "定时任务", message);
                        }
                    }
                }
            });
        }
    }

    /**
     * 保存数据库表结构及数据到文件中
     * @param databaseItem
     * @param fileFullName
     */
    public void saveDataBaseBackupSQL(DatabaseItem databaseItem, String dbName, String fileFullName) {
        String jdbcUrl = databaseItem.getJdbcUrl();
        String username = databaseItem.getUsername();
        String password = databaseItem.getPassword();

        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setMinimumIdle(5);
        dataSource.setMaximumPoolSize(10);
        dataSource.setPoolName("DatebookHikariCP");
        // 执行创建表DDL及数据脚本
        String exportDataSql = this.createTableDdlAndDataSql(dataSource, databaseItem, false, null);

        String ipPort = JdbcParseUtils.findIpPortByUrl(jdbcUrl);
        StringBuilder sb = new StringBuilder();
        sb.append("/*").append("\n");
        sb.append(" www.95coder.com Data Transfer").append("\n");
        sb.append(" Source Host           : "+ipPort).append("\n");
        sb.append(" Source Schema         : "+dbName).append("\n");
        sb.append(" Date: "+DateUtil.formatDateTime(new Date())).append("\n");
        sb.append("*/").append("\n\n");

        DbType dbType = JdbcDbTypeUtil.getDbTypeOfDruid(dataSource);
        if (dbType == DbType.kingbase){
            //添加请求头部信息
            sb.append("CREATE SCHEMA "+dbName+";").append("\n");
            sb.append("ALTER SCHEMA "+dbName+" OWNER TO system;").append("\n");
            sb.append("SET default_tablespace = '';").append("\n");
            sb.append("SET default_table_access_method = heap;").append("\n\n");
        }
        sb.append(exportDataSql);
        FileUtil.writeUtf8String(sb.toString(), fileFullName);
        log.info(">>> save SQL to file over !");
    }

    /**
     * @param dataSource
     * @param showColumns
     * @return
     */
    public String createTableDdlAndDataSql(DataSource dataSource, DatabaseItem databaseItem, boolean showColumns, String whereSql) {
        Set<String> tableNameSet = new HashSet<>();
        //查询所有表
        List<InformationTable> informationTableList = SqlInformationUtils.getInformationTableList(dataSource);
        Set<String> allTableList = informationTableList.stream().map(row -> row.getTableName()).collect(Collectors.toSet());

        //从所有备份表中移除需要忽略的表
        String ignoreTables = databaseItem.getIgnoreTables();
        if(StrUtil.isNotBlank(ignoreTables)){
            List<String> ignoreTableList = Arrays.asList(ignoreTables.split(","));
            if(ignoreTableList.size()>0){
                allTableList.removeAll(ignoreTableList);
            }
        }

        List<String> removeTableList = new ArrayList<>();
        //从所有备份表中移除需要忽略的表前缀表
        String ignoreTablePrefixes = databaseItem.getIgnoreTablePrefixes();
        if(StrUtil.isNotBlank(ignoreTablePrefixes)) {
            List<String> ignoreTablePrefixesList = Arrays.asList(ignoreTablePrefixes.split(","));
            if(ignoreTablePrefixesList!=null && ignoreTablePrefixesList.size()>0){
                for(String tablePrefix : ignoreTablePrefixesList){
                    allTableList.stream().forEach(table -> {
                        if(table.startsWith(tablePrefix.trim())){
                            removeTableList.add(table);
                        }
                    });
                }
            }
        }
        log.info("removeTableList = {}", removeTableList);
        allTableList.removeAll(removeTableList);
        log.debug("allTableList = {}", allTableList);

        //保留指定备份的表名列表
        String backupTables = databaseItem.getBackupTables();
        if(StrUtil.isNotBlank(backupTables)){
            List<String> backupTableList = Arrays.asList(backupTables.split(","));
            if(backupTableList!=null && backupTableList.size()>0){
                backupTableList.stream().forEach(row -> {
                    String table = row.trim();
                    if(allTableList.contains(table)){
                        tableNameSet.add(table);
                    }
                });
            }
        }
        //留指定备份的表名前缀的表列表
        String backupTablePrefixes = databaseItem.getBackupTablePrefixes();
        if(StrUtil.isNotBlank(backupTablePrefixes)) {
            List<String> backupTablePrefixesList = Arrays.asList(backupTablePrefixes.split(","));
            if(backupTablePrefixesList!=null && backupTablePrefixesList.size()>0){
                for(String tablePrefix : backupTablePrefixesList){
                    allTableList.stream().forEach(table -> {
                        if(table.startsWith(tablePrefix.trim())){
                            tableNameSet.add(table);
                        }
                    });
                }
            }
        }
        //如果未指定备份的表，则备份所有忽略之外的表
        if(tableNameSet.size()==0){
            tableNameSet.addAll(allTableList);
        }

        List<String> tableList = new ArrayList<>(tableNameSet);
        log.debug("backup tableList = {}", tableList);
        return SqlBackupUtils.createTableDDL2InsertAllSql(dataSource, tableList, showColumns, whereSql);
    }
}
